﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DataLayer
{
    public class DLimpediments
    {
        public DataSet impedimentslist()
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            string querystring = @"
                         SELECT        Impediments.ImpedimentID, Impediments.Titel, Impediments.Description, Impediments.TaskId, Tasks.UserId, Users.Login, Tasks.Titel AS TTitel, 
                         Tasks.TaskId AS TID
FROM                     Impediments INNER JOIN
                         Tasks ON Impediments.TaskId = Tasks.TaskId INNER JOIN
                         Users ON Tasks.UserId = Users.UserID";
            SqlConnection myconn = new SqlConnection(connStr);
            SqlCommand myquery = new SqlCommand(querystring, myconn);
            SqlDataAdapter mydatadap = new SqlDataAdapter(myquery);
            DataSet mydata = new DataSet();
            mydatadap.Fill(mydata);
            return mydata;
        } 


        public DataSet ImpedimentsDetailList(int ID)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            string query = @"
                        SELECT        Impediments.ImpedimentID, Impediments.Titel, Impediments.Description, Impediments.TaskId, Product.ProductId, Product.Name, Sprints.SprintId, 
                         Sprints.Titel AS Expr1, UserStory.UserStoryId, Tasks.TaskId AS Expr2, Tasks.Titel AS Expr3, Tasks.Status, Users.Login, UserStory.Titel AS Expr4
                         FROM Tasks 
                         INNER JOIN
                         Impediments ON Tasks.TaskId = Impediments.TaskId INNER JOIN
                         UserStory ON Tasks.UserStoryId = UserStory.UserStoryId INNER JOIN
                         Sprints INNER JOIN
                         Product ON Sprints.ProductID = Product.ProductId ON UserStory.SprintID = Sprints.SprintId INNER JOIN
                         Users ON Tasks.UserId = Users.UserID
                         WHERE Impediments.ImpedimentID = @idmeegekregen";

            SqlConnection myconn = new SqlConnection(connStr);
            SqlCommand myquery = new SqlCommand(query, myconn);
            SqlParameter param = new SqlParameter();
            param.ParameterName = "@idmeegekregen";
            param.Value = ID;
            myquery.Parameters.Add(param);
            SqlDataAdapter mydataadaptor = new SqlDataAdapter(myquery);
            DataSet mydataset = new DataSet();
            mydataadaptor.Fill(mydataset);
            return mydataset;
        }

        public void InsertImpedimentDB(string titel, string description, int taskid)
        {
            string connStr = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();

            string insertvalues = "insert into Impediments(Titel, Description , taskId) values(@titel, @description, @taskid)";
            SqlCommand query = new SqlCommand(insertvalues, conn);

            query.Parameters.AddWithValue("@titel", titel);
            query.Parameters.AddWithValue("@description", description);
            query.Parameters.AddWithValue("@taskid", taskid);

            query.ExecuteNonQuery();
            conn.Close();
        }

    }
}
